package serverSide;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;


/**
 * @author EndlessLove Computer
 *
 */
public class DataBaseTool {
public static ConnectUnit Connect;
    //DataBaseTool.Open("C_PLUS_PLUS\\SQLEXPRESS", "sa", "123456", "DoAnPhanBo", 1433);
    /**
     * @param host
     * @param username
     * @param password
     * @param database
     * @param port
     * @return
     */
    public static boolean Open(String host,String username,String password,String database, int port)
    {
         return   ((Connect =  new ConnectUnit(host, port, username, password, database))).Connect();
    }
     /**
     * @return
     */
    public static Connection getConnection(){
            
            return ConnectUnit.getConnectionReport();
    }
    /**
     * 
     */
    public static void Close()
    {
        try 
        {
            Connect.Close();
        }
        catch (SQLException ex) 
        {
            Logger.getLogger(DataBaseTool.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    /**
     * @param procName
     * @param param
     * @return
     * @throws Exception
     */
    @SuppressWarnings("deprecation")
	public static ResultSet CallProc(String procName, Param param) throws Exception
    {
        try 
        {
            String SPsql = "{ CALL  " + procName + "( ";   // for stored proc taking 2 parameters
            for(int i =0 ; i < param.getParameters().size();i++)
                SPsql += " ?,";
            SPsql = SPsql.substring(0, SPsql.length()-1) + ")}";
            CallableStatement call = Connect._connect._connect.prepareCall(SPsql);
            call.setEscapeProcessing(true);
            call.setQueryTimeout(50);
            String date = "";
            int index = 1;
            for(int i = 0 ; i < param.getParameters().size(); i++)
            {
                switch (param.At(i).Type)
                {
                    case Char:
                    	if(param.At(i).Value == null)
                    		call.setNull(index++, Types.CHAR);
                    	else
                    		call.setString(index++, param.At(i).Value);
                    	break;
                    case Nvarchar:
                    	if(param.At(i).Value == null)
                           call.setNull(index++, Types.NVARCHAR);
                        else
                        	call.setString(index++, param.At(i).Value);
                        break;
                    case Tinyint:
                    case Int:
                    case SmallInt:
                    	if(param.At(i).Value == null)
                            if(param.At(i).Type == SqlDataType.Int)
                                call.setNull(index++, Types.INTEGER);
                            else
                                call.setNull(index++, Types.SMALLINT);
                        else
                            call.setInt(index++,Integer.parseInt(param.At(i).Value));
                        break;
                     case Money:
                    	 if(param.At(i).Value == null)
                                call.setNull(index++,Types.FLOAT);
                          else 
                              call.setFloat(index++, Float.parseFloat(param.At(i).Value));
                        break;
                    case Bit:
                    	if(param.At(i).Value == null)
                            call.setBoolean(index++, true);
                        else
                            call.setBoolean(index++, false);
                        break;
                    case Date:
                    	if(param.At(i).Value == null)
                            call.setNull(index++, Types.DATE);
                        else
                        {
                            date =  param.At(i).Value;
                            call.setDate(index++, new java.sql.Date(new java.util.Date(
                                    Integer.parseInt(date.split("-")[2])- 1900,Integer.parseInt(date.split("-")[1]) - 1,Integer.parseInt(date.split("-")[0])).getTime()));
                        }
                        break;
                }
                    
            }
            return call.executeQuery();
        }
        catch (SQLException ex) 
        {
            Logger.getLogger(ConnectUnit.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
    
    /**
     * @param table
     * @param res
     */
    public static  void LoadData(Object table, ResultSet res) 
    {
         try
        {
            int count = 1;
            while (res.next()) 
            {
                ((DefaultTableModel)((JTable)table).getModel()).addRow(new Object[] 
                {
                    count++ ,res.getObject(1),res.getObject(2),res.getObject(3),res.getObject(4),res.getObject(5),res.getObject(6),res.getObject(7)
                });
            }
        }
        catch (SQLException ex) 
        {
            System.err.println(ex.getMessage());
        }  
    }
    
    /**
     * @param res
     * @return
     */
    public static  java.util.List<Object[]> ConvertToList(ResultSet res)
    {
    	java.util.List<Object[]> objs = new ArrayList<>();
    	try {
			while(res.next())
			{
				int count = res.getMetaData().getColumnCount();
				Object []o = new Object[count];
				for(int i = 0; i < count; i++)
					o[i] = res.getObject(i + 1);
				objs.add(o);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return objs;
    }

    /**
     * @param procName
     * @return
     */
    public static ResultSet CallProc(String procName)
    {
         try 
        {
            String SQL = "{CALL " + procName + "()}";
           CallableStatement call =  Connect._connect._connect.prepareCall(SQL);
           //exec [TimSach] '1', N'Vật Lý ', N'Vật Lý', null, 0, 600, 'OR' , 0
           return call.executeQuery();
        }
        catch (SQLException ex) 
        {
            Logger.getLogger(ConnectUnit.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
   
    /**
     * @param procName
     * @param param
     * @param Noreturn
     * @return
     * @throws Exception
     */
    @SuppressWarnings("deprecation")
	public static boolean CallProc(String procName, Param param, boolean Noreturn ) throws Exception
    {
        try 
        {
            String SPsql = "{ CALL  " + procName + "( ";   // for stored proc taking 2 parameters
            for(int i =0 ; i < param.getParameters().size();i++)
                SPsql += " ?,";
            SPsql = SPsql.substring(0, SPsql.length()-1) + ")}";
            CallableStatement call = Connect._connect._connect.prepareCall(SPsql);
            call.setEscapeProcessing(true);
            call.setQueryTimeout(50);
            int index = 1;
            String date = "";
            for(int i = 0 ; i < param.getParameters().size(); i++)
            {
                switch (param.At(i).Type)
                {
                    case Char:
                    	if(param.At(i).Value == null)
                    		call.setNull(index++, Types.CHAR);
                    	else
                    		call.setString(index++, param.At(i).Value);
                    	break;
                    case Nvarchar:
                    	if(param.At(i).Value == null)
                           call.setNull(index++, Types.NVARCHAR);
                        else
                        	call.setString(index++, param.At(i).Value);
                        break;
                    case Tinyint:
                    case Int:
                    case SmallInt:
                    	if(param.At(i).Value == null)
                            if(param.At(i).Type == SqlDataType.Int)
                                call.setNull(index++, Types.INTEGER);
                            else
                                call.setNull(index++, Types.SMALLINT);
                        else
                            call.setInt(index++,Integer.parseInt(param.At(i).Value));
                        break;
                     case Money:
                    	 if(param.At(i).Value == null)
                                call.setNull(index++,Types.FLOAT);
                          else 
                              call.setFloat(index++, Float.parseFloat(param.At(i).Value));
                        break;
                    case Bit:
                    	if(param.At(i).Value == null)
                            call.setBoolean(index++, true);
                        else
                            call.setBoolean(index++, false);
                        break;
                    case Date:
                    	if(param.At(i).Value == null)
                            call.setNull(index++, Types.DATE);
                        else
                        {
                            date =  param.At(i).Value;
                            call.setDate(index++, new java.sql.Date(new java.util.Date(
                                    Integer.parseInt(date.split("-")[2])- 1900,Integer.parseInt(date.split("-")[1]) - 1,Integer.parseInt(date.split("-")[0])).getTime()));
                        }
                        break;
                }
                    
            }
            call.executeUpdate();
            return true;
        }
        catch (SQLException ex) 
        {
            Logger.getLogger(ConnectUnit.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public static ResultSet ExcuteQuery(String query) throws Exception
    {
    	return Connect.Excute(query);
    }
}
